<?php
	require_once("../shared/database.php");
	// Do our best to finish as many matches as possible
	
	Database::instance()->query("SET NAMES 'utf8'");
	
	$result = Database::instance()->select(array(
			"matches.id" => "match_id",
			"items.id" => "item_id",
			"hash",
			"canonical_label",
			"label",
			"verified_match"
		))->from('matches')->leftjoin('items', array('items.match_id', 'matches.id'))->where(array('items.verified_match' => 1, 'matches.finished !=' => 1))->orderby('items.hash')->get();
	
	$match_id = null;
	$items = array();
	
	while($row = mysql_fetch_assoc($result))
	{
		if($row['match_id'] != $match_id)
		{
			// We've found a new match
			
			// Update the last match we looked over
			if($match_id)
			{
				$update = false;
				
				$label = $birth_date = $death_date = $occupation = $nationality = null;
				
				if(isset($items['grove']))
				{
					$label = 		get_value_from_catalog($items, 'grove', 'label');
					$birth_date = 	get_value_from_catalog($items, 'grove', 'Birth_Date');
					$death_date = 	get_value_from_catalog($items, 'grove', 'Death_Date');
					$occupation = 	get_value_from_catalog($items, 'grove', 'occupation');
					$nationality = 	get_value_from_catalog($items, 'grove', 'Nationality');
					
					$update = true;
				}
				else if(isset($items['musicbrainz']))
				{	
					$label = 		get_value_from_catalog($items, 'musicbrainz', 'label');
					$birth_date = 	get_value_from_catalog($items, 'musicbrainz', 'Birth_Date');
					$death_date = 	get_value_from_catalog($items, 'musicbrainz', 'Death_Date');
					$occupation = 	get_value_from_catalog($items, 'musicbrainz', 'occupation');
					$nationality = 	get_value_from_catalog($items, 'musicbrainz', 'Nationality');
					
					$update = true;
				}
				else if(count($items) == 1 && false)
				{
					// Auto confirm all items with just a single variation
					
					$keys = array_keys($items);
					$key = array_pop($keys);
					
					$label = 		get_value_from_catalog($items, $key, 'label');
					$birth_date = 	get_value_from_catalog($items, $key, 'Birth_Date');
					$death_date = 	get_value_from_catalog($items, $key, 'Death_Date');
					$occupation = 	get_value_from_catalog($items, $key, 'occupation');
					$nationality = 	get_value_from_catalog($items, $key, 'Nationality');

					$update = true;
				}
				
				// if(stripos($label, "schubert") !== FALSE && stripos($label, "franz") !== FALSE)
				// {
				// 	var_dump($match_id);var_dump($label);var_dump($items);die;
				// }
					
				
				if($update)
				{
					$query = "UPDATE matches SET scripted=1, ".
												"canonical_label=_utf8'".mysql_escape_string($label)."', ".
												"birth_date='".mysql_escape_string($birth_date)."', ".
												"death_date='".mysql_escape_string($death_date)."', ".
												"occupation='".mysql_escape_string($occupation)."', ".
												"nationality='".mysql_escape_string($nationality)."' ".
												"WHERE id='".mysql_escape_string($match_id)."'";
												
					//echo "$query\n";
					Database::instance()->query($query);
				}
			}
			
			// Update the match_id variable
			$match_id = $row['match_id'];
			$items = array();
		}
		
		$metadata_result = Database::instance()->select('*')->from('metadata')->where(array('item_id' => $row['item_id']))->get();
		$metadata = array();
		
		while($meta_row = mysql_fetch_assoc($metadata_result))
			$metadata[$meta_row['key']] = $meta_row['value'];
			
		$metadata['label'] = $row['label'];

		$catalog = strtolower(preg_replace("/[^a-zA-Z]/", "", $metadata['X0']));
		
		if(strpos($catalog, "copac") !== FALSE)
			$catalog = "copac";

		$items[$catalog] = $metadata;
	}
	
	function get_value_from_catalog($data, $cat, $key)
	{
		$value = isset($data[$cat]) && isset($data[$cat][$key]) ? $data[$cat][$key] : null;
		
		if($value == null)
			return $value;
			
		// If this is a date then we should make a best effort at parsing it
		if(in_array($key, array('Birth_Date', 'Death_Date')))	
		{
			if(preg_match("/[0-9]{4}[\-\.\/]{1}[0-9]{2}[\-\.\/]{1}[0-9]{2}/", $value))
				return $value;
			else
			{
				// Attempt to extract the details from the string
				$year = "0000";
				$month = $date = "00";
				
				if(preg_match("/[0-9]{4}/", $value, $match))
					$year = $match[0];
					
				$months = array(
					"01" => array("january", "jan"),
					"02" => array("february", "feb"),
					"03" => array("march", "mar"),
					"04" => array("april", "apr"),
					"05" => array("may"),
					"06" => array("june", "june"),
					"07" => array("july", "jul"),
					"08" => array("august", "aug"),
					"09" => array("september", "sep", "sept"),
					"10" => array("october", "oct"),
					"11" => array("november", "nov"),
					"12" => array("december", "dec"),
				);
				
				foreach($months as $m => $variants)
				{
					foreach($variants as $v)
					{
						if(stripos($value, $v) !== false)
						{
							$month = $m;
							break;
						}
					}
				}
				
				if(preg_match("/[0-9]{2}/", $value, $match))
					$date = $match[0];
					
				return "$year-$month-$date";
			}
		}
		
		return $value;
	}
?>